import time
import pymysql
from pandas.io import sql


def get_time():
    time_str =  time.strftime("%Y{}%m{}%d{} %X")
    return time_str.format("年","月","日")     #因为直接写不支持直接识别中文，才用format写


#return: 连接，游标
def get_conn():

    # 创建连接
    conn = pymysql.connect(host="127.0.0.1",
                           user="root",
                           password="123456",
                           db="cov",
                           charset="utf8")
    # 创建游标
    cursor = conn.cursor()# 执行完毕返回的结果集默认以元组显示
    return conn, cursor


def close_conn(conn, cursor):
    cursor.close()
    conn.close()

def query(sql,*args):
    """
    封装通用查询
    :param sql:
    :param args:
    :return: 返回查询到的结果，((),(),)的形式
    """
    conn, cursor = get_conn()
    cursor.execute(sql,args)
    res = cursor.fetchall()
    close_conn(conn, cursor)
    return res



#返回各省数据
def get_c2_data():
      # 因为会更新多次数据，取时间戳最新的那组数据
    sql = "select province,sum(confirm) from details " \
          "where update_time=(select update_time from details " \
          "order by update_time desc limit 1) " \
          "group by province"
    res = query(sql)
    return res

# 返回热点新闻
def getHotNews():
    # 因为会更新多次数据，取时间戳最新的那组数据
    sql = "select * from news t order by t.in_time desc"
    res = query(sql)
    return res


#返回安徽省各市数据
def get_anhui_data():
      # 因为会更新多次数据，取时间戳最新的那组数据
    sql = "select city,sum(confirm) from details t " \
          "where update_time=(select update_time from details " \
          "order by update_time desc limit 1) " \
          "AND t.province = '安徽' AND t.city <> '境外输入' " \
          "group by city"
    res = query(sql)
    return res

